package cz.byteworks.android.myway.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cz.byteworks.android.myway.pojo.Position;
import cz.byteworks.android.myway.pojo.Way;
import cz.byteworks.android.myway.util.Logger;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DataHelper {

	private DbHelper dbHelper;
	
	public DataHelper(Context context) {
		dbHelper = new DbHelper(context, "myway.db", null, 1);
	}
	
	public void insertPosition(Position position) {
		if (position == null) {
			return;
		}
		if (position.getLocateTime() == null) {
			position.setLocateTime(new Date());
		}
		position.setWayId(getActualWay());
		String QUERY = "INSERT INTO MYWAY_POSITIONS (locate_time, latitude, longitude, altitude, note, mark, fk_way_id, locality) VALUES ("+position.getLocateTime().getTime()+", "+position.getLatitude()+", "+position.getLongitude()+", "+position.getAltitude()+", "+getQuotedOrNull(position.getNote())+", "+position.getMark()+", "+position.getWayId()+", "+getQuotedOrNull(position.getLocality())+");";
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(QUERY);
		db.close();
	}
	
	public void insertNewWay(String name) {
		if (name == null) {
			return;
		}
		String QUERY = "INSERT INTO MYWAY_WAYS (create_time, name) VALUES ("+(new Date()).getTime()+", "+getQuotedOrNull(name)+");";
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(QUERY);
		db.close();
	}
	
	public Position getLatestPosition() {
		String QUERY = "SELECT locate_time, latitude, longitude, altitude, note, mark, pid, locality FROM MYWAY_POSITIONS ORDER BY pid DESC LIMIT 1";
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cur = db.rawQuery(QUERY, null);
		if (cur.getCount() == 0) {
			return null;
		}
		cur.moveToFirst();
		Position pos = new Position();
		pos.setLatitude(cur.getDouble(1));
		pos.setLongitude(cur.getDouble(2));
		pos.setAltitude(cur.getDouble(3));
		pos.setLocateTime(new Date(cur.getLong(0)));
		pos.setMark(cur.getInt(5));
		pos.setNote(cur.getString(4));
		pos.setPid(cur.getInt(6));
		pos.setLocality(cur.getString(7));
		cur.close();
		db.close();
		return pos;
	}
	
	public Position getLatestPosition(int wayId) {
		String QUERY = "SELECT locate_time, latitude, longitude, altitude, note, mark, pid, locality FROM MYWAY_POSITIONS WHERE fk_way_id = "+wayId+" ORDER BY pid DESC LIMIT 1";
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cur = db.rawQuery(QUERY, null);
		if (cur.getCount() == 0) {
			return null;
		}
		cur.moveToFirst();
		Position pos = new Position();
		pos.setLatitude(cur.getDouble(1));
		pos.setLongitude(cur.getDouble(2));
		pos.setAltitude(cur.getDouble(3));
		pos.setLocateTime(new Date(cur.getLong(0)));
		pos.setMark(cur.getInt(5));
		pos.setNote(cur.getString(4));
		pos.setPid(cur.getInt(6));
		pos.setLocality(cur.getString(7));
		cur.close();
		db.close();
		return pos;
	}
	
	public int getActualWay() {
		String QUERY = "SELECT MAX(wid) FROM MYWAY_WAYS;";
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cur = db.rawQuery(QUERY, null);
		cur.moveToFirst();
		int actualWayId = cur.getInt(0);
		cur.close();
		db.close();
		return actualWayId;
	}
	
	public List<Way> getAllWays(boolean sortDesc) {
		String SORT = null;
		if (sortDesc) {
			SORT = "DESC";
		}
		else {
			SORT = "ASC";
		}
		List<Way> wayList = new ArrayList<Way>();
		String QUERY = "SELECT wid, create_time, name FROM myway_ways ORDER BY wid "+SORT;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cur = db.rawQuery(QUERY, null);
		cur.moveToFirst();
		while (!cur.isAfterLast()) {
			Way way = new Way();
			way.setId(cur.getInt(0));
			way.setCreateTime(new Date(cur.getLong(1)));
			way.setName(cur.getString(2));
			wayList.add(way);
			cur.moveToNext();
		}
		cur.close();
		db.close();
		return wayList;
	}
	
	public List<Position> getAllPositions(int wayId, boolean sortDesc) {
		String SORT = null;
		if (sortDesc) {
			SORT = "DESC";
		}
		else {
			SORT = "ASC";
		}
		List<Position> resList = new ArrayList<Position>();
		String QUERY = "SELECT locate_time, latitude, longitude, altitude, note, mark, pid, locality FROM MYWAY_POSITIONS WHERE fk_way_id = "+wayId+" ORDER BY pid "+SORT;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cur = db.rawQuery(QUERY, null);
		cur.moveToFirst();
		while (!cur.isAfterLast()) {
			Position pos = new Position();
			pos.setLatitude(cur.getDouble(1));
			pos.setLongitude(cur.getDouble(2));
			pos.setAltitude(cur.getDouble(3));
			pos.setLocateTime(new Date(cur.getLong(0)));
			pos.setMark(cur.getInt(5));
			pos.setNote(cur.getString(4));
			pos.setPid(cur.getInt(6));
			pos.setLocality(cur.getString(7));
			resList.add(pos);
			cur.moveToNext();
		}
		cur.close();
		db.close();
		return resList;
	}
	
	
	private String getQuotedOrNull(String str) {
		if (str == null || str.trim().length() == 0) {
			return null;
		}
		return "'"+str+"'";
	}

	public void updatePosNote(int posId, String note) {
		String QUERY = "UPDATE MYWAY_POSITIONS SET note='"+note+"' WHERE pid = "+posId;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(QUERY);
		db.close();
	}
	
	public void updateWayName(String name, int wayId) {
		if (name == null) {
			return;
		}
		String QUERY = "UPDATE MYWAY_WAYS SET name='"+name+"' WHERE wid = "+wayId;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(QUERY);
		db.close();
	}

	public void deleteWay(int wayId) {
		String QUERY = "DELETE FROM MYWAY_WAYS WHERE wid = "+wayId;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(QUERY);
		QUERY = "DELETE FROM MYWAY_POSITIONS WHERE fk_way_id = "+wayId;
		db.execSQL(QUERY);
		db.close();
		Logger.debug("Way "+wayId+" has been removed");
	}
	
	public void deletePosition(int pid) {
		String QUERY = "DELETE FROM MYWAY_POSITIONS WHERE pid = "+pid;
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL(QUERY);
		db.close();
		Logger.debug("Position "+pid+" has been removed");
	}
	
	public Date getLastPositionDate(int wid) {
		String QUERY = "SELECT MAX(locate_time) FROM MYWAY_POSITIONS WHERE fk_way_id="+wid;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cur = db.rawQuery(QUERY, null);
		cur.moveToFirst();
		Date lastDate = new Date(cur.getLong(0));
		cur.close();
		db.close();
		return lastDate;
	}
	
	public int getPositionCount(int wid) {
		String QUERY = "SELECT COUNT(1) FROM MYWAY_POSITIONS WHERE fk_way_id="+wid;
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cur = db.rawQuery(QUERY, null);
		cur.moveToFirst();
		int count = cur.getInt(0);
		cur.close();
		db.close();
		return count;
	}
	
	
	
}
